package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.*;

import JDBCconnent.JDBCUtil;
import information.LotteryNumber;

public class LotteryTicketDAOJDBC implements LotteryTicketDAO {

	@Override
	public boolean buyLottery(String name, String lotteryNumber) {
		int num=getNewLotteryIssue()+1;
		 String sql="insert into "+num+"lottery(name,lottery,flag) values(?,?,?) ";
		 try (Connection con= JDBCUtil.getConnection();
	             PreparedStatement pst=con.prepareStatement(sql);
	        ) {
	                pst.setString(1,name);
	                pst.setString(2,lotteryNumber);
	                pst.setInt(3, 0);
	                pst.executeUpdate();
	        }catch (SQLException e){
	            e.printStackTrace();
	        }
	        return true;
	}

	//@Override
//	public LotteryNumber searchHistryLottery(int number) {
//		 String sql="select * from alllotterynumber";
//	        try(Connection con= JDBCUtil.getConnection();
//	        PreparedStatement pst=con.prepareStatement(sql);
//	        	){
//	        	ResultSet rs = pst.executeQuery(sql);
//	            while (rs.next()) {
//	                Integer id = rs.getInt("id");
//	                if(id.equals(number)) {
//	                	String lotteryNumber=rs.getString("lottery");
//	                	LotteryNumber lottery=new LotteryNumber(id,lotteryNumber);
//	                	return lottery;
//	                }
//	            }
//	        }catch (SQLException e){
//	            e.printStackTrace();
//	        }
//		return null;
//	}

	@Override
	public Integer getNewLotteryIssue() {
		String sql="select * from alllotterynumber";
        try(Connection con= JDBCUtil.getConnection();
        PreparedStatement pst=con.prepareStatement(sql);
        	){
        	Integer num=0;
        	ResultSet rs = pst.executeQuery(sql);
            while (rs.next()) {
                num++;
            }
            return num;
        }catch (SQLException e){
            e.printStackTrace();
        }
	return null;
}

	@Override
	public boolean drowNewLotteryTable() {
		int number=getNewLotteryIssue()+1;
		String creatsql = "CREATE TABLE "+number+"lottery("
				+ "id int not null primary key auto_increment,"
				+ "name varchar(20) not null,"
				+ "lottery varchar(20) not null,"
				+ "flag int);";
		Statement stmt = null;
		try(Connection con= JDBCUtil.getConnection();){
			stmt = con.createStatement();
			stmt.executeLargeUpdate(creatsql);
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean drowNewlotteryNumber(String newLottery) {
		// TODO 自动生成的方法存根
		String sql="insert into alllotterynumber(id,lottery) values(?,?) ";
		try (Connection con= JDBCUtil.getConnection();
	             PreparedStatement pst=con.prepareStatement(sql);
	        ) {
	                pst.setInt(1,getNewLotteryIssue()+1);
	                pst.setString(2,newLottery);
	                pst.executeUpdate();
	        }catch (SQLException e){
	            e.printStackTrace();
	        }
	        return true;
	}

	@Override
	public boolean checkUserBuyLottery(String name) {
		int num=getNewLotteryIssue()+1;
		String sql="select * from "+num+"lottery";
		try(Connection con= JDBCUtil.getConnection();
		        PreparedStatement pst=con.prepareStatement(sql);
		        	){
		        	ResultSet rs = pst.executeQuery(sql);
		            while (rs.next()) {
		                String username = rs.getString("name");
		                if(username.equals(name)) {
		                	return true;
		                }

		            }
		        }catch (SQLException e){
		            e.printStackTrace();
		        }
		        return false;
	}

	@Override
	public String getLotteryNumber(int num) {
		String sql="select * from alllotterynumber";
		 try(Connection con= JDBCUtil.getConnection();
			        PreparedStatement pst=con.prepareStatement(sql))
				 {
			        	ResultSet rs = pst.executeQuery(sql);
			            while (rs.next()) {
			                int id=rs.getInt("id");
			                if(num==id) {
			                	String lotteryNumber=rs.getString("lottery");
			                	return lotteryNumber;
			                }
			            }
			           
			        }catch (SQLException e){
			            e.printStackTrace();
			        }
				return null;
		
	}

	@Override
	public boolean exchangeLottery(String name,int num) {
		String sql1="update "+num+"lottery"+" set flag=? where name=?";
		String sql="select * from "+num+"lottery";
		try(Connection con= JDBCUtil.getConnection();
		        PreparedStatement pst=con.prepareStatement(sql);
				PreparedStatement pst1=con.prepareStatement(sql1);
		        	){
		        	ResultSet rs = pst.executeQuery(sql);
		            while (rs.next()) {
		                String username = rs.getString("name");
		                if(username.equals(name)) {
		                	pst1.setInt(1, 1);
		                	pst1.setString(2, username);
		                	pst1.executeUpdate();
		                	return true;
		                }

		            }
		        }catch (SQLException e){
		            e.printStackTrace();
		        }
		        return false;
	}

	@Override
	public boolean searchExchangeLottery(String name, int num) {
		String sql="select * from "+num+"lottery";
		try(Connection con= JDBCUtil.getConnection();
		        PreparedStatement pst=con.prepareStatement(sql);
		        	){
		        	ResultSet rs = pst.executeQuery(sql);
		            while (rs.next()) {
		                String username = rs.getString("name");
		                if(username.equals(name)) {
		                	int flag=rs.getInt("flag");
		                	if(flag==0) {
		                		return false;
		                	}else {
		                		return true;
		                	}
		                }

		            }
		        }catch (SQLException e){
		            e.printStackTrace();
		        }
		        return false;
	}

	@Override
	public String userLottery(String name, int num) {
		String sql="select * from "+num+"lottery";
		try(Connection con= JDBCUtil.getConnection();
		        PreparedStatement pst=con.prepareStatement(sql);
		        	){
		        	ResultSet rs = pst.executeQuery(sql);
		            while (rs.next()) {
		                String username = rs.getString("name");
		                if(username.equals(name)) {
		                	return rs.getString("lottery");
		                }

		            }
		        }catch (SQLException e){
		            e.printStackTrace();
		        }
		        return null;
	}

	@Override
	public Integer statsNumber(int num) {
		String sql="select * from "+num+"lottery";
		int flag=0;
		try(Connection con= JDBCUtil.getConnection();
		        PreparedStatement pst=con.prepareStatement(sql);
		        	){
		        	ResultSet rs = pst.executeQuery(sql);
		            while (rs.next()) {
		                flag++;

		            }
		        }catch (SQLException e){
		            e.printStackTrace();
		        }
		        return flag;
	}


	

}
